﻿using Models.Common;
using Models.MVC;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Web.Filters;

namespace Web.Controllers.MVC
{
    [MvcAuthentication]
    public class Bill4Controller : Controller
    {
        // GET: Bill4
        public ActionResult DetailsList(int ID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex4.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
                ,isnull((select JiHuaOrderID from BillIndex3 where ID=BillIndex4.BillIndex3ID),0) as JiHuaOrderID
FROM BillIndex4 left join Goods on BillIndex4.GoodsID=Goods.ID left join Goods as SGoods on BillIndex4.SourceGoodsID=SGoods.ID
WHERE BillIndex4.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill4IndexModel m = new Models.MVC.Bill4IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill4IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public string DetailsListAsync(int IndexID)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@IndexID",SqlDbType.Int),
            };
            parameters[0].Value = IndexID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill4BillDetailsList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        // GET: Bill4
        public ActionResult IndexList()
        {
            return View();
        }

        [HttpPost]
        public string IndexListAsync(int PageIndex = 1, int PageSize = 1000000000, string Search = "", int State = 0)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@State",SqlDbType.Int),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = Search;
            parameters[3].Value = State;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill4IndexList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult IndexEdit(int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex4.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
FROM BillIndex4 left join Goods on BillIndex4.GoodsID=Goods.ID left join Goods as SGoods on BillIndex4.SourceGoodsID=SGoods.ID
WHERE BillIndex4.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill4IndexModel m = new Models.MVC.Bill4IndexModel();
            m.CreateDate = DateTime.Now.ToString("yyyy-MM-dd");
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill4IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=3 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;
            DataTable ddt = FreeADO.DBUtility.DbHelperSQL.Query("select * from DengJi").Tables[0];
            string ds = "";
            foreach (DataRow item in ddt.Rows)
            {
                ds = ds + @"<option value=""" + item["Name"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.DengJi = ds;

            if (ID == 0)
            {
                DataTable mdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillTempletIndex where BillNameID=4 and IsDeleted=0").Tables[0];
                string ms = "";
                foreach (DataRow item in mdt.Rows)
                {
                    ms = ms + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
                }
                ViewBag.MoBan = ms;
            }
            else
            {
                ViewBag.MoBan = "";
            }



            return View();
        }
        [HttpPost]
        public ActionResult IndexEditAsync(Models.MVC.Bill4IndexModel m)
        {
            Result<string> result = Web.Common.Bill4.IndexEdit(m);
            return Json(result);
        }
        [HttpPost]
        public ActionResult IndexDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"UPDATE BillIndex4 set IsDeleted=1 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";

            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult ModifyIndexStateAsync(int ID, int State)
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            try
            {
                SqlParameter[] paras ={
                    new SqlParameter("@BillNameID",SqlDbType.Int),
                    new SqlParameter("@IndexID",SqlDbType.Int),
                    new SqlParameter("@Type",SqlDbType.Int),
                    new SqlParameter("@Str",SqlDbType.NVarChar,4000),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                                       };
                paras[0].Value = 4;
                paras[1].Value = ID;
                paras[2].Value = State;
                paras[3].Value = "";
                paras[3].Direction = ParameterDirection.InputOutput;
                paras[4].Direction = ParameterDirection.ReturnValue;

                DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_ModifyIndexState", paras, "aaa", 3000);
                if (Convert.ToInt32(paras[4].Value) == 1)
                {
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = 0;
                }
                else
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = paras[3].Value.ToString();
                    result.Data = 0;
                }
            }
            catch (Exception e)
            {
                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }

        public ActionResult BillProceduresEdit(int IndexID = 0, int ID = 0)
        {
            ViewBag.IndexID = IndexID;
            ViewBag.ID = ID;
            ViewBag.ProcedureID = 0;
            ViewBag.Remark = "";
            ViewBag.ShowIndex = 0;
            ViewBag.BiaoZhunQty = 1;
            ViewBag.GongYiID = 0;
            if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures4 where IsDeleted=0 and BillIndexID=" + IndexID)) > 0)
            {
                ViewBag.ShowIndex = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select max(ShowIndex) from BillProcedures4 where IsDeleted=0 and BillIndexID=" + IndexID)) + 1;
            }

            if (ID > 0)
            {
                DataTable fdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillProcedures4 where ID=" + ID).Tables[0];
                ViewBag.ProcedureID = fdt.Rows[0]["ProcedureID"].ToString();
                ViewBag.Remark = fdt.Rows[0]["Remark"].ToString();
                ViewBag.ShowIndex = fdt.Rows[0]["ShowIndex"].ToString();
                ViewBag.BiaoZhunQty = fdt.Rows[0]["BiaoZhunQty"].ToString();
                ViewBag.GongYiID = fdt.Rows[0]["GongYiID"].ToString();
            }
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Procedures where IsDeleted=0 and State=1 and BillNameID=4").Tables[0];
            string s = "";
            foreach (DataRow item in dt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Procedures = s;

            return View();
        }
        [HttpPost]
        public ActionResult BillProceduresEditAsync(int IndexID = 0, int ID = 0, int ProcedureID = 0, string Remark = "", int ShowIndex = 0, decimal BiaoZhunQty = 0, int GongYiID = 0)
        {
            Result<int> result = new Result<int>();
            if (BiaoZhunQty <= 0)
            {
                result.Type = AjaxResultType.Error;
                result.Content = "工时标准必须大于0！";
                result.Data = 0;
                return Json(result);
            }
            try
            {
                if (ID == 0)
                {
                    if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures4 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0")) > 0)
                    {
                        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures4 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0");
                    }

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("insert into BillProcedures4 (BillIndexID,ProcedureID,Remark,ShowIndex,BiaoZhunQty,GongYiID) values (" + IndexID + "," + ProcedureID + ",@Remark," + ShowIndex + "," + BiaoZhunQty.ToString() + "," + GongYiID + ")", new SqlParameter[1] { new SqlParameter("@Remark", Remark) });
                }
                else
                {
                    if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures4 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0 and ID<>" + ID)) > 0)
                    {
                        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures4 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0 and ID<>" + ID);
                    }
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("update BillProcedures4 set ProcedureID=" + ProcedureID + ",Remark=@Remark,ShowIndex=" + ShowIndex + @",BiaoZhunQty=" + BiaoZhunQty.ToString() + ", GongYiID=" + GongYiID + " where ID=" + ID, new SqlParameter[1] { new SqlParameter("@Remark", Remark) });
                }
                result.Type = AjaxResultType.Success;
                result.Content = "成功。";
                result.Data = 0;

            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }

        public ActionResult BillDetailsEdit(int BillProcedureID = 0, int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillDetails4 where ID=" + ID).Tables[0];
            Bill4DetailsModel m = new Bill4DetailsModel();
            m.BillProcedureID = BillProcedureID;
            m.ShaiXuanModel = FreeADO.DBUtility.DbHelperSQL.GetSingle("select Remark from BillProcedures4 where ID=" + BillProcedureID).ToString();
            m.InQty = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select dbo.Fn_Value_GetOutQty(4," + BillProcedureID + ")"));
            m.OutQty = m.InQty;
            m.OperatorUserID = Common.CookiesManagement.GetUserModel().UserID;
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill4DetailsModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
                m.BeginDate = Convert.ToDateTime(m.BeginDate) < new DateTime(2000, 01, 01) ? "" : Convert.ToDateTime(m.BeginDate).ToString("yyyy-MM-dd");
                m.EndDate = Convert.ToDateTime(m.EndDate) < new DateTime(2000, 01, 01) ? "" : Convert.ToDateTime(m.EndDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;
            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=3 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;

            return View();
        }
        [HttpPost]
        public ActionResult BillDetailsEditAsync(Bill4DetailsModel m)
        {
            Result<int> result = Web.Common.Bill4.BillDetailsEdit(m);
            return Json(result);

        }
        [HttpPost]
        public ActionResult BillProceduresDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetails4 where BillProcedureID in (" + IDs + @")");
                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillProcedures4 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillDetailsDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetails4 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        public ActionResult BillDetailsStuffList(int BillDetailsID)
        {
            ViewBag.ID = BillDetailsID;
            return View();
        }

        [HttpPost]
        public string BillDetailsStuffListAsync(int BillDetailsID)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillDetailsID",SqlDbType.Int),
            };
            parameters[0].Value = BillDetailsID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill4DetailsStuffList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult BillDetailsStuffEdit(int BillDetailsID, int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select a.Qty,a.GoodsID,b.TypeID,a.ID
FROM BillDetailsStuff4 as a inner join Goods b on a.GoodsID=b.ID
WHERE a.ID=" + ID).Tables[0];
            ViewBag.BillDetailsID = BillDetailsID;
            ViewBag.ID = ID;
            ViewBag.Qty = 0;
            ViewBag.GoodsID = 0;
            ViewBag.GoodsType = 1;
            Models.MVC.Bill4IndexModel m = new Models.MVC.Bill4IndexModel();
            if (dt.Rows.Count > 0)
            {
                ViewBag.Qty = dt.Rows[0]["Qty"].ToString();
                ViewBag.GoodsID = dt.Rows[0]["GoodsID"].ToString();
                ViewBag.GoodsType = dt.Rows[0]["TypeID"].ToString();
            }
            ViewBag.model = m;




            return View();
        }
        [HttpPost]
        public ActionResult BillDetailsStuffEditAsync(int BillDetailsID, int GoodsID, int Qty, int ID)
        {
            Result<string> result = new Result<string>();
            try
            {
                if (ID == 0)
                {
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
INSERT INTO [dbo].[BillDetailsStuff4]
           ([BillDetailsID]
           ,[GoodsID]
           ,[Qty])
     VALUES
           (
'" + BillDetailsID + @"',
'" + GoodsID + @"',
'" + Qty + @"'
)
");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";
                }
                else
                {

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
UPDATE [dbo].[BillDetailsStuff4]
   SET [BillDetailsID] = '" + BillDetailsID + @"'
      ,[GoodsID] = '" + GoodsID + @"'
      ,[Qty] = '" + Qty + @"'
 WHERE ID=" + ID + @"
");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";
                }
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillDetailsStuffDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetailsStuff4 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        public ActionResult JiaoFuList(int ID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex4.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
FROM BillIndex4 left join Goods on BillIndex4.GoodsID=Goods.ID left join Goods as SGoods on BillIndex4.SourceGoodsID=SGoods.ID
WHERE BillIndex4.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill4IndexModel m = new Models.MVC.Bill4IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill4IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public string JiaoFuListAsync(int IndexID)
        {

            GridResult result = new GridResult();

            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select ID,CONVERT(nvarchar(50),CreateTime,120) as CreateTime,Qty ,Number,Remark
from GoodsStockRunnings where BillNameID=4 and Type=1 and BillIndexID=" + IndexID + @"
order by ID desc").Tables[0];
            result.count = 0;
            result.data = dt;
            result.totalRow = new Dictionary<string, string>();
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult JiaoFuEdit(int IndexID, int ID = 0)
        {
            ViewBag.IndexID = IndexID;
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"select * from GoodsStockRunnings WHERE ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.JiaoFuModel m = new Models.MVC.JiaoFuModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.JiaoFuModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateTime = Convert.ToDateTime(m.CreateTime).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public ActionResult JiaoFuEditAsync(int IndexID, decimal Qty, int ID = 0, string Remark = "", string Number = "", int IsExec = 0, string CreateTime = "")
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            result = Web.Common.BillCommon.JiaoFuEditAsync(4, IndexID, Qty, ID, Remark, Number, IsExec, CreateTime);

            return Json(result);
        }
        [HttpPost]
        public ActionResult ShowIndexEditAsync(int IndexID, int BillProcedureID, int ShowIndex)
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            try
            {
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures4 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0")) > 0)
                {
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures4 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0");
                }

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"UPDATE BillProcedures4 set ShowIndex=" + ShowIndex + " where ID=" + BillProcedureID);
            }
            catch (Exception e)
            {
                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }
        public ActionResult ReportChengPinLv()
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select ID,Name from [Procedures] where  [Procedures].BillNameID=4 and IsDeleted=0").Tables[0];
            string s = "";
            string su = "";
            foreach (DataRow item in dt.Rows)
            {
                s = s + @"{ field: 'C_" + item["ID"].ToString() + @"_InQty', title: '上接数' },
{ field: 'C_" + item["ID"].ToString() + @"_OutQty', title: '下传数' },
{ field: 'C_" + item["ID"].ToString() + @"_FailQty', title: '废品数' },
{ field: 'C_" + item["ID"].ToString() + @"_FailRate', title: '淘汰率' },";
                su = su + @"{ field: '', title: '" + item["Name"].ToString() + @"' ,colspan:4,align:'center' },";
            }

            ViewBag.TableClo = s;
            ViewBag.TableCloUp = su;
            return View();
        }

        [HttpPost]
        public string ReportChengPinLvAsync(int PageIndex = 1, int PageSize = 1000000000, string Search = "", string BeginDate = "", string EndDate = "")
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = Search;
            parameters[3].Value = BeginDate;
            parameters[4].Value = EndDate;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill4ReportChengPinLv", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult ReportUser()
        {
            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=3 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;
            return View();
        }

        [HttpPost]
        public string ReportUserAsync(int PageIndex = 1, int PageSize = 1000000000, int UserID = 0, string BeginDate = "", string EndDate = "")
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
                                            new SqlParameter("@UserID",SqlDbType.Int),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = BeginDate;
            parameters[3].Value = EndDate;
            parameters[4].Value = UserID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill4BillDetailsListForUser", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        public ActionResult ReportForBill4()
        {
            return View();
        }

        [HttpPost]
        public string ReportForBill4Async(string BeginDate, string EndDate, int PageIndex = 1, int PageSize = 1000000000)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int)
            };
            parameters[0].Value = BeginDate;
            parameters[1].Value = EndDate;
            parameters[2].Value = PageIndex;
            parameters[3].Value = PageSize;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_ReportBill4List", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = new Dictionary<string, string>();
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        public FileResult Down(int ID)
        {


            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
declare @BillIndexID int=" + ID + @"
select bd.ID
,ROW_NUMBER() over(order by bp.showindex) as RowID
,Convert(nvarchar(10),bd.BeginDate,120) as BeginDate
,Convert(nvarchar(10),bd.EndDate,120) as EndDate
,Case when CheShiUserID>0 then Convert(nvarchar(10),bd.CheShiBeginDate,120) else '' end as CheShiBeginDate
,Case when CheShiUserID>0 then Convert(nvarchar(10),bd.CheShiEndDate,120) else '' end as CheShiEndDate
,bd.ShaiXuanModel
,bd.InQty as Qty
,bd.FailQty
,bd.ShiXiao
,ou.Name as UserName
,csu.Name as CheShiUserName
,pro.Name as ProName
from BillDetails4 as bd inner join BillProcedures4 as bp on bd.BillProcedureID=bp.ID
inner join [Procedures] as pro on bp.ProcedureID=pro.ID
left join Users as ou on bd.OperatorUserID=ou.ID
left join Users as cu on bd.CheckUserID=cu.ID
left join Users as csu on bd.CheShiUserID=csu.ID
where bp.BillIndexID=@BillIndexID
").Tables[0];
            DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex4.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
FROM BillIndex4 left join Goods on BillIndex4.GoodsID=Goods.ID left join Goods as SGoods on BillIndex4.SourceGoodsID=SGoods.ID
WHERE BillIndex4.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill4IndexModel m = new Models.MVC.Bill4IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill4IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt1))[0];
            }
            MemoryStream ms = Common.ExcelTool.Bill4ToExcel(dt, m);
            return File(ms, "application/vnd.ms-excel", m.Code + ".xls");
        }

    }
}